﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SQLite;
using System.Data;
using System.Windows.Forms;

namespace PTCScheduler
{
    class Conference
    {
       //Date, time, and location data fields
        private String date;
        public String Date
        {
            get { return date; }
            set { date = value; }
        }

        private String time;
        public String Time
        { 
            get {return time;}
            set { time = value; } 
        }

        private String location;
        public String Location
        {
            get { return location; }
            set { location = value; } 
        }

        //Student id data field
        private int studentID;
        public int StudentID
        {
            get { return studentID; }
            set { studentID = value; } 
        }

        //Teacher name datafield
        private String teacherName;
        public String TeacherName
        {
            get { return teacherName; }
            set { teacherName = value; } 
        }

        //TeacherID datafield
        private int teacherID;
        public int TeacherID
        {
            get { return teacherID; }
            set { teacherID = value; } 
        }

        //Couselor Needed datafield
        private Boolean counselorNeeded;
        public Boolean CounselorNeeded
        {
            get { return counselorNeeded; }
            set { counselorNeeded = value; } 
        }

        //Counselor name datafield
        private String counselorName;
        public String CounselorName
        {
            get { return counselorName; }
            set { counselorName = value; } 
        }

        //CounselorID datafield
        private int counselorID;
        public int CounselorID
        {
            get { return counselorID; }
            set { counselorID = value; } 
        }

        //Comments datafield
        private String comments;
        public String Comments
        { 
            get {return comments;}
            set { comments = value; } 
        }

        //Check the database for schedule conflicts datafield (boolean).
        private Boolean conflict;
        public Boolean Conflict
        {
            get { return conflict; }
            set { conflict = value; }
        }

        //SQLite Connection
        private SQLiteConnection conn;

        //SQLite Connection String
        private String connString = SystemQueries.dbConnString();

        //SQLite Transaction
        private SQLiteTransaction transaction;

        //Datafield to determine whether sqlite exception is thrown and to display MessageBox
        private Boolean exSQLite = false;
        public Boolean EXSQLite
        {
            get { return exSQLite; }
        }

        //Empty Constructor
        public Conference()
        {
            //Set all fields to null or empty
            date = "";
            time = "";
            location = "";
            studentID = 0;
            teacherName = "";
            teacherID = 0;
            counselorNeeded = false;
            counselorID = 0;
            comments = "";
            conflict = false;
        }

        //Check for scheduling conflict within the database and set the conflict
        //boolean datafield to true or false.
        public void checkConflict()
        {
            //Connect to database
            dbConn();

            //Retrieve the teacher conflict query string from the SystemQueries class and find the 
            //count for particular teacher in conference table.
            String sql = SystemQueries.teacherConflictQueryString(teacherID, date, time);

            //SQLite command to retrieve count(*)
            SQLiteCommand cmd = new SQLiteCommand();
            cmd.CommandText = sql;
            cmd.CommandType = CommandType.Text;
            cmd.Connection = conn;

            //Execute scalar to retrieve and set the value of counselor ID
            Object teacherCount = cmd.ExecuteScalar();

            //Convert to integer to retrieve count
            int count = Convert.ToInt32(teacherCount);

            //Set the conflict based on the count
            if (count == 2)
            {
                Conflict = true;
            }
            else
            {
                Conflict = false;
            }

            //Close connection.
            conn.Close();
        }

        //Method to create new appointment insert string based off of datafields.
        public String newConferenceInsertString()
        {
            if(counselorNeeded == false){
                //Set counselor id = to 0 since no counselor is needed.
                return @"INSERT INTO conferences VALUES (
                        " + teacherID + "," + studentID + ", " + NoCounselor() + ", '" + date
                          + " " + time + "', '" + location + "', 'NO', 'NO', '" + comments + "')" ;
            }

            //Retrieve and set counselor id.
            getCounselorID();

            //Set counselor id = to desired counselor since ounselor is needed.
            return @"INSERT INTO conferences VALUES (
                        " + teacherID + "," + studentID + ", " + counselorID + ",'" + date
                      + " " + time + "', '" + location + "', 'YES', 'NO', '" + comments + "')";
        }

        //Method to create the new conference using the insert string.
        public void createNewConference()
        {
            //Set sqlite exception to false by default.
            exSQLite = false;

            try
            {
                //Set the new conference insert string
                String sql = newConferenceInsertString();

                //Create new sqlite connection
                SQLiteConnection myConnection = new SQLiteConnection(SystemQueries.dbConnString());

                //Open connection
                myConnection.Open();

                //Set the SQLiteTransaction
                transaction = myConnection.BeginTransaction();

                //Create the SQLite command and set its properties
                SQLiteCommand command = new SQLiteCommand();
                command.Transaction = transaction;
                command.CommandText = sql;
                command.ExecuteNonQuery();

                //Commit the transaction
                transaction.Commit();
                transaction.Dispose();
                //Close connection
                myConnection.Close();
                myConnection.Dispose();
            }
            catch (Exception)
            {   
                //Set boolean
                exSQLite = true;
                MessageBox.Show("This Conference has already been scheduled and the the Confirmation emails have been sent.s"); 
            }
        }

        //Method to set counselor id to 0
        public int NoCounselor()
        {
            //Set counselor needed to NO
            counselorNeeded = false;

            //Set ID to 0
            return 0;
        }

        //Method to retrieve teacher id
        public void getTeacherID()
        {
            //Connect to database
            dbConn();

            //Retrieve index of comma to find various parts of name.
            int commaIndex = teacherName.IndexOf(',');

            //Retrieve first and last name from the full counselor name string.
            String fname = teacherName.Substring(commaIndex + 2);
            String lname = teacherName.Substring(0, commaIndex);

            //Retrieve the teacher query string from the SystemQueries class and set the
            //teacher combobox contents to teacher names.
            String sql = SystemQueries.teacherIDQueryString(fname, lname);

            //SQLite command to retrieve counselor id
            SQLiteCommand cmd = new SQLiteCommand();
            cmd.CommandText = sql;
            cmd.CommandType = CommandType.Text;
            cmd.Connection = conn;

            //Execute scalar to retrieve and set the value of counselor ID
            Object cID = cmd.ExecuteScalar();

            //Convert to integer to retrieve count
            teacherID = Convert.ToInt32(cID);

            //Close connection.
            conn.Close();
        }

        //Method to retrieve counselor id 
        public void getCounselorID()
        {
            //Counselor is needed if method is invoked.
            counselorNeeded = true;

            //Connect to database
            dbConn();

            //Retrieve index of comma to find various parts of name.
            int commaIndex = counselorName.IndexOf(',');

            //Retrieve first and last name from the full counselor name string.
            String fname = counselorName.Substring(commaIndex + 2);
            String lname = counselorName.Substring(0, commaIndex);

            //Retrieve the teacher query string from the SystemQueries class and set the
            //teacher combobox contents to teacher names.
            String sql = SystemQueries.counselorIDQueryString(fname,lname);

            //SQLite command to retrieve counselor id
            SQLiteCommand cmd = new SQLiteCommand();
            cmd.CommandText = sql;
            cmd.CommandType = CommandType.Text;
            cmd.Connection = conn;

            //Execute scalar to retrieve and set the value of counselor ID
            Object cID = cmd.ExecuteScalar();

            //Convert to integer
            counselorID = Convert.ToInt32(cID);

            //Close connection.
            conn.Close();
        }

        //Connect to database
        public void dbConn()
        {
            //Connect to the database.
            conn = new SQLiteConnection(connString);

            //Open connection
            conn.Open();
        }

        //Format date to a more detailed style.
        private String formatDate(String date)
        {
            DateTime dtDate = DateTime.Parse(date);

            return dtDate.ToString("D");
        }

        //Method to build the email body. 
        public String buildEmailBodyString()
        {
            //Format the time.
            DateTime dt = DateTime.Parse(time);

            if(CounselorNeeded == false)
            {
                return @"<b>Parent-Teacher Conference Details</b>" +  "<br>" +  "<b>-------------------------------------------</b>" + "<br>" +
                "- Date: " + formatDate(Date) + "<br>" +
                    "- Time: " + dt.ToString("hh:mm tt") + "<br>" +
                    "- Location: " + Location + "<br>" +
                    "- Student (ID): " + StudentID + "<br>";
            }

            return @"<b>Parent-Teacher Conference Details</b>" + "<br>" +  "<b>---------------------------------------------</b>" + "<br>" +
                "- Date: " + formatDate(Date) + "<br>" +
                    "- Time: " + dt.ToString("hh:mm tt") + "<br>" +
                    "- Location: " + Location + "<br>" +
                    "- Counselor: " + CounselorName + "<br>" +
                    "- Student (ID): " + StudentID + "<br>";
        }

    }
}
